#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2022
# @Author  : lingfeng.hu@nokia-sbell.com)
# @Link    : TBD
# @Version : $Id$

# https://jiradc.ext.net.nokia.com/rest/api/2/issue/FPB-662472
import argparse
import os
from shutil import copy
from atlassian import Jira
from openpyxl import Workbook, load_workbook
from datetime import datetime
from openpyxl.chart import LineChart, Reference
from openpyxl.chart.axis import DateAxis
from FeatureBuildsSchedule import buildFeatureBuildsTimeline, getFeatureBuild

teamMapping = {
    "1174" : "L3_4G FT ET_HZ64_SG4",
    "5253" : "L3_5GL3ET_HZH_Jazz",
    "5254" : "L3_5GL3ET_HZH_Blues",
    "5255" : "L3_5GL3ET_HZH_Squad C",
    "5351" : "L3_5GL3ET_HZH_Rock",
    "6261" : "L3_5GL3ET_HZH_SHIELD",
    "5235" : "L3_5GL3_SG09_SHA_Groovyard",
    "8364" : "L3_5G L3 ET_HZ64_SG4",		
    "5236" : "L3_5GL3_SG09_SHA_Typhoon",
    "5237" : "L3_5GL3_SG09_SHA_Weeasy",
    "6624" : "L3_5G L3_HZ64_FTH1",
    "6625" : "L3_5G ET HZ64_FTH_5G",
    "6626" : "L3_5G L3 EFS_HZ64_FTH1",
    "8342" : "L3_5GL3ET_NJG_Beams",
    "8050" : "L3_5GL3_SG08_HZH_PER",
    "5233" : "L3_5GL3_SG08_HZH_BARRETT",
    "6262" : "L3_5GL3_SG14_HZH_DRAGONBALL",
    "8536" : "L3_5GL3_SG08_HZH_Center",
    "5234" : "L3_5GL3_SG08_HZH_BEE",
    "5160" : "L3_ASIRAP_NJG_SW1_L3",
    "5219" : "L3_5GL3_SG01_HZH_ACE",			
    "5220" : "L3_5GL3_SG01_HZH_Carry",
    "5250" : "L3_5GL3_SG05_HZH_Blink",			
    "6389" : "L3_5GL3EFS_HZH_CPEFS",
    "6391" : "L3_5GL3_SG05_HZH_Easy",
    "8514" : "L3_5GL3_SG05_HZH_Frame",
    "5243" : "L3_5GL3_SG12_HZH_GELIVABLE",
    "5244" : "L3_5GL3_SG12_HZH_Superman",
    "4780" : "L3_FZRTSP_NJG_Glimmer_L3",
    "5160" : "L3_ASIRAP_NJG_SW1",
    "8043" : "L3_5G L3 ET_HZ64_Woodpecker",
    "8499" : "L3_5G L3 EFS_HZ64_FTH11",
    "7955" : "L3_5GL3_NJG_Mars",
    "7962" : "L3_5GL3_NJG_Nemo",
    "8469" : "L3_5GL3_NJG_Mercury",
    "8619" : "L3_5G L3_HZ64_FTH11",
    "8042" : "L3_5G L3_HZ64_FTH25",
    "8470" : "L3_5G L3_HZ64_TrunkForce",
    "8376" : "L3_5G L3_HZ64_FTH99",
    "8044" : "L3_5G L3 EFS_HZ64_NorthStar",
    "8041" : "L3_5G L3_HZ64_FTH24",
    "8482" : "L3_5GL3_NJG_Jupiter",
    "8182" : "DCM_5G_L3_EFS_BJ_JobRotation",
    "8183" : "DCM_5G_L3_BJ_JobRotation",
    "5241" : "L3_5GL3_SG11_SHA_ROAD",
    "8038" : "L3_5G L3_HZ64_FTH5",
    "8040" : "L3_5G L3_HZ64_FTH22",
    "5228" : "L3_5G L3_HZ64_STAR",
    "5242" : "L3_5GL3_SG11_SHA_TAO",
    "5231" : "L3_5G L3_HZ64_GAEA",
    "5252" : "L3_5GL3_SG11_SHA_Giraffe",
    "8413" : "L3_5G L3_HZ64_BalanceGold",
    "5222" : "L3_5GL3_SG03_HZH_Eagle",
    "5224" : "L3_5GL3_SG03_HZH_Trident",
    "5225" : "L3_5GL3_SG03_HZH_Venus",
    "5226" : "L3_5GL3_SG04_HZH_Dragonhawk",
    "8039" : "L3_5G L3_HZ64_FTH21",
    "5227" : "L3_5GL3_SG04_HZH_Griffin",
    "6522" : "L3_5GL3_SG02_HZH_KongFu",
    "6801" : "L3_5GL3_SG02_HZH_MAMBA",
    "8463" : "L3_5G L3 ET_HZ64_SG2",
    "5223" : "L3_5G L3_HZ64_Ocean",
    "5229" : "L3_5G L3_HZ64_BINGO",	
    "8580" : "L3_5GL3EFS_NJG_Venus",
    "8596" : "DCM_5G_L3_ET_BJ_JobRotation",
    "6697" : "L2L3_ET_CN2",
    "1510" : "L3_DU_HZ",
    "2244" : "ECE_L3_HZ",
    "6874" : "L2L3_ET_CN3",
    "852"  : "L3_CU_HZ",
    "864"  : "L2L3_EFS_HZ",
    "868"  : "L2L3_EFS_Wro",
    "8731" : "L3_5G L3_HZ64_SCMCI",
    "8806" : "L3_5GL3_SG14_HZH_DRAGONFLY"
}

def getItemId(issue):
    return issue['fields']['customfield_38702'] if issue['fields']['customfield_38702'] else ''

def getKey(issue):
    return issue['key'] if issue['key'] else ''

def getSummary(issue):
    return issue['fields']['summary'] if issue['fields']['summary'] else ''

def getPlannedSystemRelease(issue):
    try:
        return issue['fields']['customfield_38724'][0]['value']
    except:
        return ''

def getBusinessPrio1(issue):
    return issue['fields']['customfield_38716'] if issue['fields']['customfield_38716'] else ''

def getAssignee(issue):
    return issue['fields']['assignee']['displayName'] if issue['fields']['assignee'] else ''

def getStatus(issue):
    return issue['fields']['status']['name'] if issue['fields']['status'] else ''

def getTeam(issue):
    if issue['fields']['customfield_29790']:
        if issue['fields']['customfield_29790'] in teamMapping.keys():
            return teamMapping[issue['fields']['customfield_29790']]
        else:
            return issue['fields']['customfield_29790']
    else: 
        return ''

def getLabels(issue):
    return ','.join(issue['fields']['labels'])

def getTimeRemaining(issue):
    return issue['fields']['customfield_43291'] if issue['fields']['customfield_43291'] else 0

def getTimeLogged(issue):
    return issue['fields']['customfield_43290'] if issue['fields']['customfield_43290'] else 0

def getOriginalEstimate(issue):
    return issue['fields']['customfield_43292'] if issue['fields']['customfield_43292'] else 0

def getStartFb(issue):
    try:
        return int(issue['fields']['customfield_38694'])
    except:
        return 0

def getEndFb(issue):
    try:
        return int(issue['fields']['customfield_38693'])
    except:
        return 0

def getFbCommitStatus(issue):
    return issue['fields']['customfield_38727']

def getStretchReason(issue):
    return issue['fields']['customfield_38727']

def getDelayReason(issue):
    return issue['fields']['customfield_38759'][0]['value'] if issue['fields']['customfield_38759'] else ''

def getDelayExplanation(issue):
    return issue['fields']['customfield_38755']

def getRfcFB(issue):
    try:
        return int(issue['fields']['customfield_43490']['value'])
    except:
        return 0

def getRfcStatus(issue):
    return issue['fields']['customfield_38728']['value'] if issue['fields']['customfield_38728'] else ''

def getCompetenceArea(issue):
    return issue['fields']['customfield_38690']['value'] if issue['fields']['customfield_38690'] else ''

def getFs0Ee(issue):
    return issue['fields']['customfield_38737'] if issue['fields']['customfield_38737'] else ''

def getFs2Ee(issue):
    return issue['fields']['customfield_38692'] if issue['fields']['customfield_38692'] else ''

def getFS2Status(issue):
    return issue['fields']['customfield_38705'] if issue['fields']['customfield_38705'] else ''

def getFotLeader(issue):
    return issue['fields']['customfield_38695']['displayName'] if issue['fields']['customfield_38695'] else ''

def getWorkType(issue):
    return issue['fields']['customfield_38368']['value'] if issue['fields']['customfield_38368'] else ''

def getIssueType(issue):
    return issue['fields']['issuetype']['name'] if issue['fields']['issuetype'] else ''

def getFeatureId(issue):
    return issue['fields']['customfield_37381'] if issue['fields']['customfield_37381'] else ''

def getReqArea(issue):
    return issue['fields']['customfield_38712'] if issue['fields']['customfield_38712'] else ''

def getActType(issue):
    return issue['fields']['customfield_38750']['value'] if issue['fields']['customfield_38750'] else ''

def getText2(issue):
    return issue['fields']['customfield_38727'] if issue['fields']['customfield_38727'] else ''

def getSpecEndFB(issue):
    return issue['fields']['customfield_38739'] if issue['fields']['customfield_38739'] else ''

def getCustomer(issue):
    customerList = []
    if issue['fields']['customfield_32006']:
        for customer in issue['fields']['customfield_38693']:
            customerList.append(customer['value'])
        return ','.join(customerList)
    else:
        return ''

def getTargetReleases(issue):
    try:
        return issue['fields']['customfield_38710']
    except AttributeError:
        return ''

def getCreatedTime(issue):
    return issue['fields']['created'] if issue['fields']['created'] else ''

def getUpdatedTime(issue):
    return issue['fields']['updated'] if issue['fields']['updated'] else ''

def getResolvedTime(issue):
    return issue['fields']['resolutiondate'] if issue['fields']['resolutiondate'] else ''

def getRfcEe(issue):
    print('getRfcEe: {}'.format(getKey(issue)))
    rfcRemainEe = 0
    rfcLogEe = 0
    rfcTime = ''
    rfcFound = False
    logEe = 0
    timeRemain = 0
    if getCompetenceArea(issue) == '':
        return (rfcRemainEe, rfcLogEe, rfcTime)
    changeLog = jira.get_issue_changelog(getKey(issue))
    for history in changeLog['histories']:
        if rfcFound:
            break
        for item in history['items']:
            createdTime = datetime.strptime(history['created'], '%Y-%m-%dT%H:%M:%S.%f%z').astimezone().strftime('%Y-%m-%d')
            if item['field'] == 'Σ Time Remaining (h)':
                timeRemain = float(item['toString']) if item['toString'] != '' else 0

            if item['field'] == 'Σ Logged Effort (h)':
                # print(item['toString'])
                logEe = float(item['toString']) if item['toString'] != '' else 0

            if item['field'] == 'Release Committed Status':
                if item['toString'] == 'Ready for Commitment':
                    rfcRemainEe = timeRemain
                    rfcLogEe = logEe
                    rfcTime = createdTime
                    rfcFound = True
                break

    return (rfcRemainEe, rfcLogEe, rfcTime)

# jirashell -s https://jiradc.ext.net.nokia.com -u <user> -p <passwd>
# issue=jira.issue('FPB-263466')
# issue.fields.customfield_38759[0].value


today = datetime.now().strftime("%Y-%m-%d")
startTime = datetime.now()

def getIssues(jqlRequest):
    now = datetime.now()
    print("fetch {}-{}".format('0', nokiaJiraDcResultLimitNum))
    issuesJira = jira.jql(jqlRequest, jqlFields, start=0, limit=nokiaJiraDcResultLimitNum)
    print("spend time: {} in this round".format(datetime.now() - now))
    # print('=================================')
    # print(len(issuesJira['issues']))
    # for issue in issuesJira['issues']:
    #     print(issue['key'])
    
    totalIssuesNum = issuesJira['total']
    print("total: {}".format(totalIssuesNum))

    # totalIssuesNum = issuesJira['total']
    if int(nokiaJiraDcResultLimitNum) < totalIssuesNum:
        continueRoundCount = totalIssuesNum//int(nokiaJiraDcResultLimitNum)
        for round in range(continueRoundCount):
            startIndex = int(nokiaJiraDcResultLimitNum) * (round+1)
            print("fetch next: {}-{}".format(startIndex, startIndex+int(nokiaJiraDcResultLimitNum)-1))
            startTime = datetime.now()
            issuesJira['issues'].extend(jira.jql(jqlRequest, jqlFields, start=startIndex, limit=nokiaJiraDcResultLimitNum)['issues'])
            print("spend time: {} in this round".format(datetime.now() - startTime))
    
    return issuesJira['issues']

nokiaJiraDcResultLimitNum = 1000

jqlFeatureFieldsMapping = [
        ('Item ID', 'customfield_38702'),
        ('Key', 'issuekey'),
        ('Summary', 'summary'),
        ('Planned System Release', 'customfield_38724'),
        ('Business Priority 1', 'customfield_38716'),
        ('Assignee', 'assignee'),
        ('Status', 'status'),
        ('Team', 'customfield_29790'),
        ('Labels', 'labels'),
        ('ΣTime Remaining (h)', 'customfield_43291'),
        ('ΣLogged Effort (h)', 'customfield_43290'),
        ('ΣOriginal Estimate (h)', 'customfield_43292'),
        ('Start FB', 'customfield_38694'),
        ('End FB', 'customfield_38693'),
        ('FB Commit Status', 'customfield_38758'),
        ('Stretch Goal Reason', 'customfield_43893'),
        ('Delay Reason', 'customfield_38759'),
        ('Delay Explanation', 'customfield_38755'),
        ('Release committed FB', 'customfield_43490'),
        ('Release Committed Status', 'customfield_38728'),
        ('Competence Area', 'customfield_38690'),
        ('FS0 EE (h)', 'customfield_38737'),
        ('FS2 EE (h)', 'customfield_38692'),
        ('FOT Leader', 'customfield_38695'),
        ('Work Type', 'customfield_38368'),
        ('Issue Type', 'issuetype'),
        ('Feature ID', 'customfield_37381'),
        ('Requirement Area', 'customfield_38712'),
        ('Activity Type', 'customfield_38750'),
        ('Text 2', 'customfield_38727')
        ]
# xxx is windows account, yyy is password
userName = 'xxx'
passWord = 'yyy'
jira = Jira(url='https://jiradc.ext.net.nokia.com', username=userName, password=passWord)

parser = argparse.ArgumentParser(description='Feature Report.', formatter_class=argparse.ArgumentDefaultsHelpFormatter)
# parser.add_argument('-d', '--directory', default=r'C:\Users\linhu\OneDrive - Nokia\Documents\workspace\pythonCoding\5g_cp_utils\Planning\FusionTools', help='working directory')
parser.add_argument('-d', '--directory', default=os.getcwd(), help='working directory')
parser.add_argument('-f', '--featureId', required=True, help='feature id')
args = parser.parse_args()

workingDir = args.directory
os.chdir(workingDir)

featureWd = args.featureId
featureId = '"' + featureWd + '*' + '"'
jqlRequest = '"Feature ID"' + '~' + featureId
# print(jqlRequest)
jqlFields = [fieldMapping[1] for fieldMapping in jqlFeatureFieldsMapping]
# issuesFeature = getIssues(jqlRequest)
# print(len(issuesFeature))
featureBuildSchedule = buildFeatureBuildsTimeline()
wb = Workbook()
ws_backlog_CA = wb.active
ws_backlog_CA.title = "Backlog_All"
if not os.path.exists(featureWd):
    os.mkdir(featureWd)
wbWeekBurndownOutputFile = os.path.join(featureWd, featureId.replace('*', '').replace('"', '') + '_' + 'week' + '_' + 'burndown' + '.xlsx')
wbDateBurndownOutputFile = os.path.join(featureWd, featureId.replace('*', '').replace('"', '') + '_' + 'date' + '_' + 'burndown' + '.xlsx')
if os.path.exists(wbWeekBurndownOutputFile):
    wbWeekBurndown = load_workbook(filename=wbWeekBurndownOutputFile)
else:
    wbWeekBurndown = Workbook()

if os.path.exists(wbDateBurndownOutputFile):
    wbDateBurndown = load_workbook(filename=wbDateBurndownOutputFile)
else:
    wbDateBurndown = Workbook()

wbBurndownList = [(wbWeekBurndown, 'week'), (wbDateBurndown, 'date')]

def exportCAIssueToExcel(ws, issue, rowIndex, columnIndex):
        # print(issue['key'])
        ws.cell(row=rowIndex, column=columnIndex).value = getItemId(issue)
        ws.cell(row=rowIndex, column=columnIndex+1).value = getKey(issue)
        ws.cell(row=rowIndex, column=columnIndex+2).value = getSummary(issue)
        ws.cell(row=rowIndex, column=columnIndex+3).value = getPlannedSystemRelease(issue)
        ws.cell(row=rowIndex, column=columnIndex+4).value = getBusinessPrio1(issue)
        ws.cell(row=rowIndex, column=columnIndex+5).value = getAssignee(issue)
        ws.cell(row=rowIndex, column=columnIndex+6).value = getStatus(issue)
        ws.cell(row=rowIndex, column=columnIndex+7).value = getTeam(issue)
        ws.cell(row=rowIndex, column=columnIndex+8).value = getLabels(issue)
        ws.cell(row=rowIndex, column=columnIndex+9).value = getTimeRemaining(issue)
        ws.cell(row=rowIndex, column=columnIndex+10).value = getTimeLogged(issue)
        ws.cell(row=rowIndex, column=columnIndex+11).value = getOriginalEstimate(issue)
        ws.cell(row=rowIndex, column=columnIndex+12).value = getStartFb(issue)
        ws.cell(row=rowIndex, column=columnIndex+13).value = getEndFb(issue)
        ws.cell(row=rowIndex, column=columnIndex+14).value = getFbCommitStatus(issue)
        ws.cell(row=rowIndex, column=columnIndex+15).value = getStretchReason(issue)
        ws.cell(row=rowIndex, column=columnIndex+16).value = getDelayReason(issue)
        ws.cell(row=rowIndex, column=columnIndex+17).value = getDelayExplanation(issue)
        ws.cell(row=rowIndex, column=columnIndex+18).value = getRfcFB(issue)
        ws.cell(row=rowIndex, column=columnIndex+19).value = getRfcStatus(issue)
        ws.cell(row=rowIndex, column=columnIndex+20).value = getCompetenceArea(issue)
        ws.cell(row=rowIndex, column=columnIndex+21).value = getFs0Ee(issue)
        ws.cell(row=rowIndex, column=columnIndex+22).value = getFs2Ee(issue)
        ws.cell(row=rowIndex, column=columnIndex+23).value = getFotLeader(issue)
        ws.cell(row=rowIndex, column=columnIndex+24).value = getWorkType(issue)
        ws.cell(row=rowIndex, column=columnIndex+25).value = getIssueType(issue)
        ws.cell(row=rowIndex, column=columnIndex+26).value = getFeatureId(issue)
        ws.cell(row=rowIndex, column=columnIndex+27).value = getReqArea(issue)
        ws.cell(row=rowIndex, column=columnIndex+28).value = getActType(issue)
        ws.cell(row=rowIndex, column=columnIndex+29).value = getText2(issue)
        (rfcRemainEe, rfcLogEe, rfcTime) = getRfcEe(issue)
        ws.cell(row=rowIndex, column=columnIndex+30).value = rfcRemainEe
        ws.cell(row=rowIndex, column=columnIndex+31).value = rfcLogEe
        ws.cell(row=rowIndex, column=columnIndex+32).value = rfcTime


class CompetenceArea:
    def __init__(self):
        self.name = ''
        self.remainEE = 0
        self.logEE = 0
        self.endFB = 0
        self.startFB = 9999
        self.status = ''
        self.rfcRemainEe = 0
        self.rfcLogEe = 0
        self.rfcTime = ''

    def setName(self, name):
        self.name = name
    
    def setLogEE(self, logEE):
        self.logEE = logEE
    
    def setRemainEE(self, remainEE):
        self.remainEE = remainEE

    def setEndFB(self, endFB):
        self.endFB = endFB
    
    def setStartFB(self, startFB):
        self.startFB = startFB

    def setStatus(self, status):
        self.status = status

    def setRfcRemainEe(self, rfcRemainEe):
        self.rfcRemainEe = rfcRemainEe

    def getRfcRemainEe(self):
        return self.rfcRemainEe

    def setRfcLogEe(self, rfcLogEe):
        self.rfcLogEe = rfcLogEe

    def getRfcLogEe(self):
        return self.rfcLogEe

    def setRfcTime(self, rfcTime):
        self.rfcTime = rfcTime

    def getRfcTime(self):
        return self.rfcTime

def buildCAList(issues):
    caList = {}
    for issue in issues:
        caName = getCompetenceArea(issue)
        if caName != '':
            timeRemainEE = getTimeRemaining(issue)
            timeLogEE = getTimeLogged(issue)
            startFB = getStartFb(issue)
            endFB = getEndFb(issue)
            status = getStatus(issue)
            issueType = getIssueType(issue)
            (rfcRemainEe, rfcLogEe, rfcTime) = getRfcEe(issue)

            if caName in caList.keys():
                if status != 'Obsolete' and issueType == 'Competence Area':
                    caList[caName].remainEE += timeRemainEE
                    caList[caName].logEE += timeLogEE
                    caList[caName].rfcRemainEe += rfcRemainEe
                    caList[caName].rfcLogEe += rfcLogEe
                    if caList[caName].rfcTime < rfcTime:
                        caList[caName].rfcTime = rfcTime
                    if caList[caName].endFB < endFB:
                        caList[caName].endFB = endFB
                    if caList[caName].startFB > startFB:
                        caList[caName].startFB = startFB
            else:
                oneCa = CompetenceArea()
                oneCa.setName(caName)
                oneCa.setRemainEE(timeRemainEE)
                oneCa.setLogEE(timeLogEE)
                oneCa.setRfcRemainEe(rfcRemainEe)
                oneCa.setRfcLogEe(rfcLogEe)
                if status != 'Obsolete' and issueType == 'Competence Area':
                    oneCa.setStartFB(startFB)
                    oneCa.setEndFB(endFB)
                    oneCa.setRfcTime(rfcTime)
                    oneCa.setStatus(status)
                caList[caName] = oneCa
        
    return caList
    
    # return set(filter(None, caList))

def genBacklogCAOutput(ws):
    print("Fetch backlogCA")
    backlog_CaHeaders = [
        ('Item ID', 'customfield_38702'),
        ('Key', 'issuekey'),
        ('Summary', 'summary'),
        ('Planned System Release', 'customfield_38724'),
        ('Business Priority 1', 'customfield_38716'),
        ('Assignee', 'assignee'),
        ('Status', 'status'),
        ('Team', 'customfield_29790'),
        ('Labels', 'labels'),
        ('ΣTime Remaining (h)', 'customfield_43291'),
        ('ΣLogged Effort (h)', 'customfield_43290'),
        ('ΣOriginal Estimate (h)', 'customfield_43292'),
        ('Start FB', 'customfield_38694'),
        ('End FB', 'customfield_38693'),
        ('FB Commit Status', 'customfield_38758'),
        ('Stretch Goal Reason', 'customfield_43893'),
        ('Delay Reason', 'customfield_38759'),
        ('Delay Explanation', 'customfield_38755'),
        ('Release committed FB', 'customfield_43490'),
        ('Release Committed Status', 'customfield_38728'),
        ('Competence Area', 'customfield_38690'),
        ('FS0 EE (h)', 'customfield_38737'),
        ('FS2 EE (h)', 'customfield_38692'),
        ('FOT Leader', 'customfield_38695'),
        ('Work Type', 'customfield_38368'),
        ('Issue Type', 'issuetype'),
        ('Feature ID', 'customfield_37381'),
        ('Requirement Area', 'customfield_38712'),
        ('Activity Type', 'customfield_38750'),
        ('Text 2', 'customfield_38727'),
        ('Rfc RemainEe(h)', 'RfcRemainEe'),
        ('Rfc LogEe(h)', 'RfcLogEe'),
        ('Rfc Time', 'RfcTime')
        ]
    columnIndex = 1
    for header in backlog_CaHeaders:
        ws.cell(row=1, column=columnIndex).value = header[0] + '(' + header[1] + ')'
        columnIndex += 1
    
    rowIndex = 2
    issues = getIssues(jqlRequest)
    for issue in issues:
        exportCAIssueToExcel(ws, issue, rowIndex=rowIndex, columnIndex=1)
        rowIndex += 1
        # print(rowIndex)
    
    return issues

def removeDuplicateRecordByDate(date, ws):
    dateColumn = 1
    for i in range(1, ws.max_column+1):
        if ws.cell(row=1, column=i).value == 'Date':
            dateColumn = i
            break
    for row in range(1, ws.max_row+1):
        if ws.cell(row=row, column=dateColumn).value == date:
            ws.delete_rows(row)
            # print('=======removeDuplicateRecordByDate=======')

def removeDuplicateRecordByWeek(wk, ws):
    weekColumn = 1
    for i in range(1, ws.max_column+1):
        if ws.cell(row=1, column=i).value == 'Week':
            weekColumn = i
            break
    for row in range(1, ws.max_row+1):
        if ws.cell(row=row, column=weekColumn).value == wk:
            ws.delete_rows(row)
            # print('=======removeDuplicateRecordByWeek=======')

def getWeekNumber(date):
    return datetime.strptime(date, '%Y-%m-%d').isocalendar()[1]

def buildBurndownChart(caList, ws):
    # caList = buildCAList(issues)
    rowIndex = 1
    colIndex = 1
    latestDate = datetime.now().strftime("%Y-%m-%d")
    # latestDate = '20220306'
    removeDuplicateRecordByDate(latestDate, ws)
    # print(caList.items())
    # print('max_row: {}, max_column: {}'.format(ws.max_row, ws.max_column))
    if ws.cell(row=1, column=1).value == 'Date' and ws.cell(row=1, column=2).value != '':
        # print(ws.max_row, ws.max_column)
        rowIndex = ws.max_row + 1
    else:
        ws.cell(row=rowIndex, column=colIndex).value = 'Date'
        colIndex = 2
        for ca in caList.items():
            ws.cell(row=1, column=colIndex).value = ca[1].name
            colIndex += 1
        rowIndex += 1
    
    ws.cell(row=rowIndex, column=1).value = latestDate
    colIndex = 2
    for ca in caList.items():
        foundCa = False
        for col in range(1, ws.max_column+1):
            if ca[1].name == ws.cell(row=1, column=col).value:
                ws.cell(row=rowIndex, column=col).value = ca[1].remainEE
                foundCa = True
                break
        if not foundCa:
            print('New CA created compared to last time')
            print('ca: {}'.format(ca[1].name))
            ws.cell(row=1, column=ws.max_column+1).value = ca[1].name
            ws.cell(row=rowIndex, column=ws.max_column).value = ca[1].remainEE

def buildCaBurndownChart(ca, view, ws):
    rowIndex = 1
    colIndex = 1
    fbColumn = 1
    weekColumn = fbColumn + 1
    dateColumn = weekColumn + 1
    remainEEColumn = dateColumn + 1
    logEEColumn = remainEEColumn + 1
    startFBColumn = logEEColumn + 2
    endFBColumn = startFBColumn + 1
    rfcRemainEeColumn = endFBColumn + 2
    rfcLogEeColumn = rfcRemainEeColumn + 1
    rfcTimeColumn = rfcLogEeColumn + 1
    latestDate = datetime.now().strftime('%Y-%m-%d')
    # latestDate = '2022-03-07'
    calendarWeek = 'cw' + str(getWeekNumber(latestDate))
    if view == 'week':
        removeDuplicateRecordByWeek(calendarWeek, ws)
    else:
        removeDuplicateRecordByDate(latestDate, ws)
    # print('max_row :{}, max_column: {}'.format(ws.max_row, ws.max_column))

    # if ws.cell(row=1, column=1).value == 'FB' and ws.cell(row=1, column=2).value != '':
    #     rowIndex = ws.max_row + 1
    # else:
    ws.cell(row=rowIndex, column=fbColumn).value = 'FB'
    ws.cell(row=rowIndex, column=weekColumn).value = 'Week'
    ws.cell(row=rowIndex, column=dateColumn).value = 'Date'
    ws.cell(row=rowIndex, column=remainEEColumn).value = 'Remain EE'
    ws.cell(row=rowIndex, column=logEEColumn).value = 'Log EE'
    ws.cell(row=rowIndex, column=startFBColumn).value = 'startFB'
    ws.cell(row=rowIndex, column=endFBColumn).value = 'endFB'
    ws.cell(row=rowIndex, column=rfcRemainEeColumn).value = 'RfcRemainEe'
    ws.cell(row=rowIndex, column=rfcLogEeColumn).value = 'RfcLogEe'
    ws.cell(row=rowIndex, column=rfcTimeColumn).value = 'RfcTime'
    # rowIndex += 1
    rowIndex = ws.max_row + 1
    
    ws.cell(row=rowIndex, column=fbColumn).value = getFeatureBuild(latestDate, featureBuildSchedule).getFB()
    ws.cell(row=rowIndex, column=weekColumn).value = calendarWeek
    ws.cell(row=rowIndex, column=dateColumn).value = latestDate
    ws.cell(row=rowIndex, column=remainEEColumn).value = ca[1].remainEE
    ws.cell(row=rowIndex, column=logEEColumn).value = ca[1].logEE
    ws.cell(row=2, column=startFBColumn).value = ca[1].startFB
    ws.cell(row=2, column=endFBColumn).value = ca[1].endFB
    ws.cell(row=2, column=rfcRemainEeColumn).value = ca[1].rfcRemainEe
    ws.cell(row=2, column=rfcLogEeColumn).value = ca[1].rfcLogEe
    ws.cell(row=2, column=rfcTimeColumn).value = ca[1].rfcTime

    caChart = LineChart()
    caChart.title = ca[0]
    caChart.style = 12
    caChart.y_axis.title = "Hours"
    caChart.y_axis.crossAx = 100
    if view == 'date':
        caChart.x_axis = DateAxis(crossAx=100)
        caChart.x_axis.number_format = 'd-mmm'
        caChart.x_axis.majorTimeUnit = "days"
        caChart.x_axis.title = "Date"
        plotMinColumn = dateColumn
    else:
        caChart.x_axis.title = "Week"
        plotMinColumn = weekColumn
    data = Reference(ws, min_col=remainEEColumn, min_row=1, max_col=logEEColumn, max_row=ws.max_row)
    caChart.add_data(data, titles_from_data=True)
    caChart.series[0].graphicalProperties.line.solidFill = 'FF0000'
    caChart.series[0].marker.symbol = "triangle"
    caChart.series[0].marker.graphicalProperties.line.solidFill = 'FF0000'
    caChart.series[1].marker.symbol = "circle"
    caChart.series[1].marker.graphicalProperties.line.solidFill = '008000'
    caChart.series[1].graphicalProperties.line.solidFill = '008000'
    catData = Reference(ws, min_col=plotMinColumn, min_row=2, max_row=ws.max_row)
    caChart.set_categories(catData)
    ws._charts.clear()
    ws.add_chart(caChart, 'H3')

issues = genBacklogCAOutput(ws_backlog_CA)
caList = buildCAList(issues)
for oneWb in wbBurndownList:
    if 'Burndown' in oneWb[0].sheetnames:
        wsBurndown = oneWb[0]['Burndown']
    else:
        wsBurndown = oneWb[0].active
        wsBurndown.title = "Burndown"
    view = oneWb[1]
    buildBurndownChart(caList, wsBurndown)
    
    for ca in caList.items():
        if ca[0] in oneWb[0].sheetnames:
            wsCaBurndown = oneWb[0][ca[0]]
        else:
            wsCaBurndown = oneWb[0].create_sheet(title=ca[0])
        
        buildCaBurndownChart(ca, view, wsCaBurndown)

snapshotCollectionTime = datetime.now().strftime('%Y%m%d%H%M%S')
wbOutputFile = os.path.join(featureWd, featureId.replace('*', '').replace('"', '') + '_' + snapshotCollectionTime + '.xlsx')
wb.save(wbOutputFile)
wbWeekBurndown.save(wbWeekBurndownOutputFile)
wbDateBurndown.save(wbDateBurndownOutputFile)
copy(wbWeekBurndownOutputFile, wbWeekBurndownOutputFile.split('.')[0]+'_'+datetime.now().strftime('%Y%m%d%H%M%S')+'.xlsx')
copy(wbDateBurndownOutputFile, wbDateBurndownOutputFile.split('.')[0]+'_'+datetime.now().strftime('%Y%m%d%H%M%S')+'.xlsx')
print("Elapsed Time: {}".format(datetime.now() - startTime))


print('collection done in {}'.format(wbOutputFile))
print('collection done in {}'.format(wbWeekBurndownOutputFile))
print('collection done in {}'.format(wbDateBurndownOutputFile))